package persistence.database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import core.exception.SaveException;
import persistence.ContainerDAO;
import persistence.ContainerDTO;
import persistence.DataPersistor;

/**
 * Container DAO for database persistence
 * @author Sabrina
 *
 */
public class DBContainerDAO implements ContainerDAO {

	/**
	 * Create an instance of the DBContainerDAO
	 */
	public DBContainerDAO()
	{
		
	}
	
	/**
	 * @inheritDoc
	 */
	@Override
	public void addContainer(ContainerDTO dto) throws SaveException {
		String query = "INSERT INTO Containers(Name, ParentID,ThreeMonthValue, ThreeMonthUnit) " +
				"values (?,?,?,?)";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			stmt = ((DBTransactionManager)DataPersistor.instance().
					getTransactionManager()).getConnection().prepareStatement(query);
			stmt.setString(1, dto.getContainerName());
			stmt.setInt(2, dto.getParentID());
			stmt.setFloat(3, dto.getThreeMonthSupplyValue());
			stmt.setString(4, dto.getThreeMonthSupplyUnit());
			
			stmt.execute();
			
			query = "SELECT ContainerID FROM Containers order by ContainerID DESC LIMIT 1";
			
			stmt = ((DBTransactionManager)DataPersistor.instance().
					getTransactionManager()).getConnection().prepareStatement(query);
			rs = stmt.executeQuery();
			if(rs.next()){
				dto.setContainerID(rs.getInt(1));
			}
			else{
				throw new SaveException("Could not determine container id");
			}
		} catch (SQLException e) {
			throw new SaveException(e.getMessage());
		}
		finally {
			try {
				if(rs != null) rs.close();
				if(stmt != null) stmt.close();
			}
			catch (SQLException e) {
				//ignore
			}
		}
		

	}

	/**
	 * @inheritDoc
	 */
	@Override
	public void updateContainer(ContainerDTO dto) throws SaveException {
		String query = "UPDATE Containers SET Name = ?, ThreeMonthValue = ?, " +
				"ThreeMonthUnit = ? WHERE ContainerID = ?";
		PreparedStatement stmt = null;
		
		try {
			stmt = ((DBTransactionManager)DataPersistor.instance().
					getTransactionManager()).getConnection().prepareStatement(query);
			stmt.setString(1, dto.getContainerName());
			stmt.setFloat(2, dto.getThreeMonthSupplyValue());
			stmt.setString(3, dto.getThreeMonthSupplyUnit());
			stmt.setInt(4, dto.getContainerID());
			
			stmt.execute();
		} catch (SQLException e) {
			throw new SaveException(e.getMessage());
		}
		finally {
			try {
				if(stmt != null) stmt.close();
			}
			catch (SQLException e) {
				//ignore
			}
		}
	}

	/**
	 * @inheritDoc
	 */
	@Override
	public void removeContainer(ContainerDTO dto) throws SaveException {
		String query = "DELETE FROM Containers WHERE ContainerID=?";
		PreparedStatement stmt = null;
		
		try {
			stmt = ((DBTransactionManager)DataPersistor.instance().
					getTransactionManager()).getConnection().prepareStatement(query);
			stmt.setInt(1,dto.getContainerID());			
			stmt.execute();
		}
		catch(SQLException e){
			throw new SaveException(e.getMessage());
		}
		finally {
			try {
				if(stmt != null) stmt.close();
			}
			catch (SQLException e) {
				//ignore
			}
		}
	}

}
